package com.example.exceldemo.utils.easy;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.exceldemo.exception.ServiceException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;

import static org.docx4j.convert.out.pdf.viaXSLFO.Conversion.log;

/**
 * @author xiaohuang
 * @date 2024/7/14
 */
public class ExcelUtils {
    private final static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 导出带有合并单元格的Excel文件。
     *
     * @param fileName      文件名，不包含扩展名
     * @param excelList     数据集合，用于填充Excel表格
     * @param excel         实体类类型，定义了Excel表头和数据结构
     * @param mergeColIndex 需要合并的列索引数组，指定哪些列需要合并
     * @param mergeRowIndex 从第几行开始合并，指定合并的起始行
     */
    public static void exportMergeExcel(String fileName, Collection<?> excelList, Class<?> excel, int[] mergeColIndex, int mergeRowIndex) {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");

            // 设置表头和内容样式
            WriteCellStyle headStyle = new WriteCellStyle();
            headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            WriteCellStyle bodyStyle = new WriteCellStyle();
            bodyStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 创建ExcelWriter对象并配置合并单元格和样式策略
            ExcelWriter writer = null;
            ServletOutputStream out = response.getOutputStream();
            try {
                writer = EasyExcel.write(out)
                        .needHead(true)
                        .excelType(ExcelTypeEnum.XLSX)
                        .registerWriteHandler(new ExcelMergeCustomerCellHandler(mergeColIndex, mergeRowIndex))
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headStyle, bodyStyle))
                        .build();

                // 写入数据到指定Sheet
                WriteSheet sheet = EasyExcel.writerSheet(fileName).head(excel).sheetNo(1).build();
                writer.write(excelList, sheet);
            } catch (Exception e) {
                throw new ServiceException("导出异常：" + e.getMessage());
            } finally {
                try {
                    if (writer != null) {
                        writer.finish();
                    }
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    throw new ServiceException("关闭输出流异常：" + e.getMessage());
                }
            }
        } catch (Exception e) {
            throw new ServiceException("全局导出异常：" + e.getMessage());
        }
    }

    /**
     * 导出简单的Excel文件。
     *
     * @param exportList 数据集合，用于填充Excel表格
     * @param excel      实体类类型，定义了Excel表头和数据结构
     * @param fileName   文件名，不包含扩展名
     * @param sheetName  Sheet名称
     */
    public static void export(Collection<?> exportList, Class<?> excel, String fileName, String sheetName) {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));

            try (OutputStream outputStream = response.getOutputStream()) {
                EasyExcel.write(outputStream, excel)
                        .sheet(sheetName)
                        .doWrite(exportList);
            }
        } catch (UnsupportedEncodingException e) {
            logger.error("导出文件时编码错误: {}", fileName, e);
        } catch (IOException e) {
            logger.error("导出文件时出现IO错误: {}", fileName, e);
        } catch (Exception e) {
            logger.error("导出文件时出现一般性错误: {}", fileName, e);
        }
    }

    /**
     * 导出多个Sheet的Excel文件（与`export`方法功能重复，建议合并）。
     *
     * @param exportList 数据集合，用于填充Excel表格
     * @param excel      实体类类型，定义了Excel表头和数据结构
     * @param fileName   文件名，不包含扩展名
     * @param sheetName  Sheet名称
     */
    public static void exportMany(Collection<?> exportList, Class<?> excel, String fileName, String sheetName) {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));

            try (OutputStream outputStream = response.getOutputStream()) {
                EasyExcel.write(outputStream, excel)
                        .sheet(sheetName)
                        .doWrite(exportList);
            }
        } catch (UnsupportedEncodingException e) {
            logger.error("导出文件时编码错误: {}", fileName, e);
        } catch (IOException e) {
            logger.error("导出文件时出现IO错误: {}", fileName, e);
        } catch (Exception e) {
            logger.error("导出文件时出现一般性错误: {}", fileName, e);
        }
    }

    /**
     * Excel导入操作
     *
     * @param inputStream Excel文件的输入流
     * @param clazz       Excel数据映射到的对象类型
     * @param headRowNumber 从Excel文件中读取数据的起始行号，默认为1
     * @return 包含从Excel文件中读取的数据的List
     */
    public static <T> List<T> simpImport(InputStream inputStream, Class<T> clazz, Integer headRowNumber,Integer sheetNum) {
        if (headRowNumber == null) headRowNumber = 1;
        try (InputStream is = inputStream) {
            // 同步读取所有数据到List中
            List<T> list = EasyExcel.read(is).head(clazz).headRowNumber(headRowNumber).doReadAllSync();
            return list;
        } catch (IOException e) {
            logger.error("读取Excel文件失败: {}", e.getMessage(), e);
            throw new ServiceException("读取Excel文件失败: " + e.getMessage(), e);
        } catch (Exception e) {
            logger.error("读取Excel文件时发生意外错误: {}", e.getMessage(), e);
            throw new ServiceException("读取Excel文件时发生意外错误: " + e.getMessage(), e);
        }
    }

    /**
     * 创建Excel表格的样式策略。
     *
     * @return 水平居中的表头和内容样式策略
     */
    public static HorizontalCellStyleStrategy formatExcel() {
        // 表头格式设置
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        headWriteCellStyle.setTopBorderColor((short) 3892127);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 15);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 15);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setWrapped(false);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 创建自定义的Excel单元格样式。
     *
     * @param workbook  工作簿对象
     * @param fontsize  字体大小
     * @param overbold  是否加粗字体
     * @param center    是否水平居中
     * @param headColor 是否设置表头背景颜色
     * @param border    是否添加边框
     * @return 自定义的单元格样式
     */
    public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontsize, boolean overbold, boolean center, boolean headColor, boolean border) {
        XSSFCellStyle style = workbook.createCellStyle();

        if (border) {
            style.setBorderBottom(BorderStyle.MEDIUM);
            style.setBorderLeft(BorderStyle.MEDIUM);
            style.setBorderTop(BorderStyle.MEDIUM);
            style.setBorderRight(BorderStyle.MEDIUM);
        }

        if (center) {
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setFontName("宋体");
        if (overbold) {
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        style.setFont(font);

        if (headColor) {
            style.setFillForegroundColor(new XSSFColor(new java.awt.Color(178, 184, 210, 255)));
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        return style;
    }

    /**
     * 将从Excel中读取的数据转换为指定类型的对象列表
     * 此方法主要用于将Excel数据（以LinkedHashMap列表形式读取）转换为指定类型的对象列表
     * 它通过反射机制读取目标类的字段，并根据字段上的ExcelProperty注解将Excel数据映射到对象中
     *
     * @param objects 从Excel中读取的数据，以LinkedHashMap列表形式表示
     * @param clazz 指定要转换的目标对象类型
     * @return 转换后的指定类型对象列表
     */
    public static <T> List<T> convertList(List<LinkedHashMap> objects, Class<T> clazz) {
        List<T> results = new ArrayList<>(objects.size());
        try {
            // 用于存储索引与字段的映射关系，以提高查找效率
            Map<String, Field> objIndex = new HashMap<>();
            // 获取转化实体字段信息集合
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                // 根据实体上Easy Excel的ExcelProperty注解中的索引值对应excel读取数据的值
                int index = field.getAnnotation(ExcelProperty.class).index();
                // 设置字段可编辑
                field.setAccessible(true);
                // 将索引与字段的映射关系存入map中，以便后续快速查找
                objIndex.put(String.valueOf(index), field);
            }

            T obj = null;
            // 遍历从Excel中读取的数据，将其转换为指定类型的对象
            for (LinkedHashMap o : objects) {
                obj = clazz.newInstance();
                for (Object key : o.keySet()) {
                    // 如果表格索引与字段注解指定索引一样则赋值
                    if (objIndex.containsKey(key)) {
                        Object object = o.get(key);
                        Object value = null;
                        Field field = objIndex.get(key);
                        // 对读取的数据进行空值判断和类型转换
                        if (ObjectUtil.isEmpty(object)) {
                            continue;
                        }
                        Class<?> type = field.getType();
                        String replace = object.toString();
                        // 有特殊需要处理的字段类型则在此进行处理
                        if (type == BigDecimal.class) {
                            value = "--".equals(replace) ? null : new BigDecimal(replace.replace(",", ""));
                        } else if (type == Integer.class) {
                            // String强转Integer会报错，所以需要单独进行转化
                            value = "--".equals(replace) ? null : Integer.valueOf(replace.replace(",", ""));
                        } else {
                            value = object;
                        }
                        // 将转换后的值设置到对象的相应字段中
                        field.set(obj, value);
                    }
                }
                // 将转换后的对象添加到结果列表中
                results.add(obj);
            }
        } catch (Exception e) {
            // 记录错误日志，并抛出自定义异常
            log.error("字段解析失败", e);
            throw new ServiceException("字段解析失败:" + e.getMessage());
        }
        // 返回转换后的对象列表
        return results;
    }

}
